﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;

namespace CommunitySystem
{
    public class CanGongBO
    {
        public ObservableCollection<CanGongModel> GetCanGongModel(int id, string orgids)
        {
            ObservableCollection<CanGongModel> rights = new ObservableCollection<CanGongModel>();
            string sql = "";
            sql = string.Format(@"select row_number()over(order by c.id ) Sec, c.ID,c.Name,c.ShangJiOrgID,
case when o.Name IS null then ShangJiName else o.Name end ShangJiName,Status,QueRenDate ,createdate,c.orgid
 from cangong c left join OrgForHR o on c.ShangJiOrgID = o.ID
where 1=1");
            if (id != 0) sql += " and c.id = " + id;
            if (!string.IsNullOrEmpty(orgids)) sql += " and '" + orgids + "' like '%' + CONVERT(varchar, c.orgid)+ '%'";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CanGongModel.FillEntity(dr));
            }
            return rights;
        }

        public void InsertCanGong(CanGongModel model)
        {
            string sql = string.Format(@"insert into OrgForHR (Name,Type,ParentID) values
('{0}','{1}',{2});select @@IDENTITY", model.Name, "参公系统", 83);
            DataSet ds = new DataSet();
            ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                int orgid = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

                string sql1 = string.Format(@"INSERT INTO CanGong
           ([Name]
           ,[ShangJiOrgID]
           ,[ShangJiName]
           ,[Status]
           ,[CreateDate],OrgID)
     VALUES
           ('{0}'
           ,{1}
           ,'{2}'
           ,'未过渡'
           ,getdate(),{3}) ", model.Name, model.ShangJiOrgID, model.ShangJiName, orgid);
                if (model.ShangJiOrgID != 0)
                {
                    string sql2 = " select UserID,OrgCode from UserInfo where OrgCode like '%" +
                                  model.ShangJiOrgID + "%'";
                    ds = AccessHelper.GetInstance().ExcuteDataSet(sql2);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        string userinfoids = "";
                        foreach (DataRow r in ds.Tables[0].Rows)
                        {
                            foreach (string i in r["OrgCode"].ToString().Split(','))
                            {
                                if (i == model.ShangJiOrgID.ToString())
                                {
                                    userinfoids += r["UserID"] + ",";
                                    continue;
                                }
                            }
                           
                        }
                        sql1 += " ;update UserInfo set OrgCode = OrgCode + '," + orgid.ToString() +
                                    "' where UserID in (" + userinfoids.Substring(0,userinfoids.Length - 1) + ")";
                    }
                }
                AccessHelper.GetInstance().ExcuteDataSet(sql1);
            }

        }

        public void UpdateCanGong(CanGongModel model)
        {
            string sql =
                string.Format(
                    @"UPDATE CanGong
   SET [Name] = '{0}'
      ,[ShangJiOrgID] = {1}
      ,[ShangJiName] = '{2}'
      ,[Status] = '{3}'
      ,[CreateDate] = '{4}'
 WHERE id = {5};
                    UPDATE OrgForHR set Name = '{0}' where id = {6}",
                    model.Name, model.ShangJiOrgID, model.ShangJiName, model.Status, model.CreateDate,
                    model.ID, model.OrgID);
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }

        public void DeleteCanGong(int id, int orgid)
        {
            string sql =
                string.Format(
                    @"Delete from CanGong WHERE id = {0};Delete from OrgForHR WHERE id = {1};", id, orgid);
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }

        public ObservableCollection<CanGongDetail> GetCanGongDetailModel(int orgid, int cangongid, int id)
        {
            ObservableCollection<CanGongDetail> rights = new ObservableCollection<CanGongDetail>();
            string sql = "";
            sql = string.Format(@"select row_number()over(order by id ) Sec,* from CanGongDetail where 1=1");
            if (orgid != 0) sql += " and orgid = " + orgid;
            if (cangongid != 0) sql += " and orgid = (select orgid from cangong where id = " + cangongid + ")";
            if (id != 0) sql += " and id = " + id;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CanGongDetail.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<CanGongDetail> GetCanGongDetailModel1(int cangongid,int status)
        {
            ObservableCollection<CanGongDetail> rights = new ObservableCollection<CanGongDetail>();
            string sql = "";
            sql = string.Format(@"select row_number()over(order by id ) Sec,* from CanGongDetail where 1=1");
            if (cangongid != 0) sql += " and orgid = (select orgid from cangong where id = " + cangongid + ")";
            sql += " and status = " + status;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CanGongDetail.FillEntity(dr));
            }
            return rights;
        }


        public void InsertCheGaiDetail(CanGongDetail model)
        {
            if (ValiIdCard(model.CardNO))
            {
                model.Gender = Checks.IsMan(model.CardNO) ? "男" : "女";
                model.Birthday = Convert.ToDateTime(Checks.GetBirthDay(model.CardNO).ToString());
            }
            string sql = string.Format(@"INSERT INTO CanGongDetail
           ([Name]
           ,[CardNO]
           ,[OldDuty]
           ,[Political]
           ,[GuoDuStyle]
           ,[Demo]
           ,[Status]
           ,[OrgID],Gender,Birthday)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,0
           ,{6},'{7}'
           ,'{8}')", model.Name, model.CardNO, model.OldDuty, model.Political, model.GuoDuStyle, model.Demo, model.OrgID, model.Gender, model.Birthday);
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }

        public void UpdateCheGaiDetail(CanGongDetail model)
        {
            if (ValiIdCard(model.CardNO))
            {
                model.Gender = Checks.IsMan(model.CardNO) ? "男" : "女";
                model.Birthday = Convert.ToDateTime(Checks.GetBirthDay(model.CardNO).ToString());
            }
            string sql =
                string.Format(
                    @"UPDATE CanGongDetail
   SET [Name] = '{0}'
      ,[CardNO] = '{1}'
      ,[OldDuty] = '{2}'
      ,[Political] = '{3}'
      ,[GuoDuStyle] = '{4}'
      ,[Demo] = '{5}',Gender = '{6}',Birthday = '{7}' where id = {8}",
                    model.Name, model.CardNO, model.OldDuty, model.Political, model.GuoDuStyle,
                    model.Demo, model.Gender, model.Birthday, model.ID);
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }

        public void DeleteCanGongDetail(string id)
        {
            string sql = @"Delete from CanGongDetail WHERE id in (" + id + ")";
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }

        public void UpdateCheGaiDetailStatus(string ids, string type)
        {
            string sql = "";
            if (type == "提交")
                sql = @"UPDATE CanGongDetail set status = 1 where id in (" + ids + ")";
            if (type == "确认")
                sql = @"UPDATE CanGongDetail set status = 2 where id in (" + ids + ")";
            if (type == "退回")
                sql = @"UPDATE CanGongDetail set status = 3 where id in (" + ids + ")";
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }

        public void UpdateCheGaiStatus(int id, string type, DateTime? dt, string orgname)
        {
            DataSet ds = new DataSet();
            string sql = "";
            if (type == "确认")
            {
                sql = string.Format(@"UPDATE CanGong set status = '已过渡',QueRenDate = '{1}' where id = {0}", id, dt);
                ObservableCollection<CanGongDetail> models = GetCanGongDetailModel1(id, 1);
                if (models != null && models.Count > 0)
                {
                    foreach (CanGongDetail i in models)
                    {
                        string sql1 = "";
                        sql1 = string.Format(
                            @";insert into Emp_BasicInfo (Name,CardNo,Gender,Birthday,Political,OrgID,OrganizationType,
NowWorkDate,HuoDeFangShi,EmpIdentity,EmpStatus) values ('{0}','{1}','{2}','{3}','{4}',{5},'参照管理事业','{6}','参公过渡','参照公务员法管理事业单位工作人员','在职');select @@IDENTITY", i.Name, i.CardNO, i.Gender, i.Birthday, i.Political,
                                                                                                                          i.OrgID, dt);
                        
                        ds = AccessHelper.GetInstance().ExcuteDataSet(sql1);
                        int empid = 0;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            empid = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                        }
                        string sql2 = "";
                        sql2 =
                            string.Format(
                                @"
                                  insert into Emp_Duty (EmpbasicID,OKDate,OrganizationName,ServiceMode,JobDate,RXRZWSXSJ,JobStatus) values
                                  ({8},'{6}','{7}','任职定级','{6}','{6}','在任');
                                  insert into Emp_Resume (EmpbasicID,BeginDate,Unit) values ({8},'{6}','{7}');

                            INSERT INTO CheGai
           ([EmpID]
           ,[OldZhiJi]
           ,[Status]
           ,[Demo]
           ,[RelationID],keyid,type,CreateDate)
     select {8},ed.dutylevel,'未导出','新增人员',{8},
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='开卡表' and emp.orgid = 
      {5}) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='开卡表' and emp.orgid = 
      {5}) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'开卡表',getdate()
 from  dbo.Emp_BasicInfo emp
inner join orgforhr o on emp.orgid = o.id
     left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任'
      where emp.ID = {8}",
                                                                                                                          i.Name, i.CardNO, i.Gender, i.Birthday, i.Political,
                                                                                                                          i.OrgID, dt, orgname, empid);
                        AccessHelper.GetInstance().ExcuteDataSet(sql2); ;
                    }
                }
            }
            if (type == "退回")
            {
                sql = string.Format(@"UPDATE CanGong set status = 3 where id = {0}", id);
            }
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }
        #region 判断性别和出生日期
        public class Checks
        {
            //是否男

            public static bool IsMan(string id)
            {
                if (id.Length == 18)
                    return (int.Parse(id.Substring(16, 1)) % 2) == 1 ? true : false;
                return (int.Parse(id.Substring(14, 1)) % 2) == 1 ? true : false;
            }

            //获取生日
            public static DateTime GetBirthDay(string id)
            {
                if (id.Length == 18)
                {
                    var birthday = id.Substring(6, 4) + "-" + id.Substring(10, 2) + "-" + id.Substring(12, 2);
                    return Convert.ToDateTime(birthday);
                }
                else
                {
                    var birthday = id.Substring(6, 2) + "-" + id.Substring(8, 2) + "-" + id.Substring(10, 2);
                    return Convert.ToDateTime(birthday);
                }
            }

        }
        /// <summary>
        /// 验证字符串是否为空或者只包含空格
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool IsStringMissing(string value)
        {
            return
                String.IsNullOrEmpty(value) ||
                value.Trim() == String.Empty;
        }
        /// <summary>
        /// 验证身份证号码
        /// </summary>
        /// <param name="idCard"></param>
        /// <returns></returns>
        public static bool ValiIdCard(string idcard)
        {
            if (IsStringMissing(idcard)) return false;
            if (idcard == null || (idcard.Length != 15 && idcard.Length != 18))
            {
                return false;
            }

            if (idcard.Length == 18)
            {
                System.Text.RegularExpressions.Regex rg = new System.Text.RegularExpressions.Regex(@"^\d{17}(\d|x|X)$");
                System.Text.RegularExpressions.Match mc = rg.Match(idcard);
                if (!mc.Success)
                {
                    return false;
                }

                if (int.Parse(idcard.Substring(6, 4)) < 1850 || int.Parse(idcard.Substring(6, 4)) > DateTime.Now.Year + 1)
                {
                    return false; // 不允许 1850年以前的日期，以及不允许当前年＋2以后的日期

                }

                try
                {
                    DateTime.Parse(idcard.Substring(6, 4) + "-" + idcard.Substring(10, 2) + "-" + idcard.Substring(12, 2));
                }
                catch
                {
                    return false;
                }

                string idcard15 = idcard.Substring(0, 6) + idcard.Substring(8, 9);

                //if (GetLongIDCardNumber(idcard15) != idcard)
                //{
                //    return false;
                //}

            }
            else if (idcard.Length == 15)
            {
                System.Text.RegularExpressions.Regex rg = new System.Text.RegularExpressions.Regex(@"^\d{15}");
                System.Text.RegularExpressions.Match mc = rg.Match(idcard);
                if (!mc.Success)
                {
                    return false;
                }

                try
                {
                    DateTime.Parse("19" + idcard.Substring(6, 2) + "-" + idcard.Substring(8, 2) + "-" + idcard.Substring(10, 2));
                }
                catch
                {
                    return false;
                }
            }

            string[] aCity = new string[]{null,null,null,null,null,null,null,null,null,null,null,
											 "北京","天津","河北","山西","内蒙古",null,null,null,null,null,
											 "辽宁","吉林","黑龙江",null,null,null,null,null,null,null,
											 "上海","江苏","浙江","安微","福建","江西","山东",null,null,null,
											 "河南","湖北","湖南","广东","广西","海南",null,null,null,
											 "重庆","四川","贵州","云南","西藏",null,null,null,null,null,null,
											 "陕西","甘肃","青海","宁夏","新疆",null,null,null,null,null,
											 "台湾",null,null,null,null,null,null,null,null,null,
											 "香港","澳门",null,null,null,null,null,null,null,null,"国外"};
            if (aCity[int.Parse(idcard.Substring(0, 2))] == null)
            {
                return false;
            }

            return true;
        }
        public static string GetLongIDCardNumber(string idcard)
        {
            //		计算方法
            //		(1)十七位数字本体码加权求和公式
            //			   S = Ai * Wi, i = 2, ... , 18
            //		Y = mod(S, 11)
            //		i: 表示号码字符从右至左包括校验码字符在内的位置序号
            //			   Ai:表示第i位置上的身份证号码字符值

            //					  Wi:表示第i位置上的加权因子
            //							 i: 18 17 16 15 14 13 12 11 10 9 8 7  6 5 4 3 2 1
            //		Wi: 7  9 10  5  8  4  2  1  6 3 7 9 10 5 8 4 2 1
            //		(2)校验码字符值的计算
            //			   Y:      0 1 2 3 4 5 6 7 8 9 10
            //		校验码: 1 0 X 9 8 7 6 5 4 3  2

            if (!ValiIdCard(idcard))
            {
                return null;
            }

            if (idcard.Length == 18)
            {
                return idcard;
            }

            idcard = idcard.Substring(0, 6) + "19" + idcard.Substring(6, 9);

            int[] Wi = { 7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1 };
            string[] CK = { "1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2" };

            int sig = 0;
            int i;

            for (i = 0; i < 17; i++)
            {
                sig += Wi[i] * int.Parse(idcard.Substring(i, 1));
            }
            int y = sig % 11;

            for (i = 0; i < 11; i++)
            {
                if (y == i)
                {
                    idcard += CK[i];
                }
            }

            return idcard;
        }
        #endregion
    }
}
